from flask import Flask,request,render_template,json,jsonify
import time
from flask_sqlalchemy import SQLAlchemy
# 初始化
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:123456@10.30.59.136:3306/wdr"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["JSON_AS_ASCII"] = False
db = SQLAlchemy(app)
# 原始数据表映射
class QQ(db.Model):
    __tablename__ = "qq"
    id = db.Column(db.INT,primary_key=True)
    qq = db.Column(db.TEXT)
    qname = db.Column(db.TEXT)
    gqq = db.Column(db.TEXT)
    gqname = db.Column(db.TEXT)
    messter = db.Column(db.TEXT)
    time = db.Column(db.TEXT)
    uuid = db.Column(db.TEXT)

# 定义函数：获取济职高考群发言前十：折线图+饼状图
def jzLine():
    qs = db.session.execute("select * from  (select count(*) as num,qq,qname  from qq   where gqq='168754589'  group by qq)as e order by num desc  limit 10;")
    datas = []
    value = []
    name = []
    qss = {}
    for x in qs:
        obj = {}
        obj["value"] = x[0]
        obj["name"] = x[1]
        datas.append(obj)
    for x in datas:
        value.append(x["value"])
        name.append(x["name"])
    qss["value"] = value
    qss["name"] = name
    return qss
# 定义函数：获取济职高考群发言前十：饼状图+词云图
def jzPie():
    qs = db.session.execute("select * from  (select count(*) as num,qq,qname  from qq   where gqq='168754589'  group by qq)as e order by num desc  limit 10;")
    datas = []
    for x in qs:
        obj = {}
        obj["value"] = x[0]
        obj["name"] = x[2]
        datas.append(obj)
    return datas
# 定义函数：获取小屋发言前五折线图
def xwqwLine():
    qw = db.session.execute("select * from  (select count(*) as num,qq,qname  from qq   where gqq='1003645830'  group by qq)as e order by num desc  limit 5;")
    datas = []
    value = []
    name = []
    qws = {}
    for x in qw:
        obj = {}
        obj["value"] = x[0]
        obj["name"] = x[1]
        datas.append(obj)
    for x in datas:
        value.append(x["value"])
        name.append(x["name"])
    qws["value"] = value
    qws["name"] = name
    return qws
# 定义函数：获取小屋发言前五饼状图
def xwqwPie():
    qw = db.session.execute("select * from  (select count(*) as num,qq,qname  from qq   where gqq='1003645830'  group by qq)as e order by num desc  limit 5;")
    datas = []
    value = []
    name = []
    qws = {}
    for x in qw:
        obj = {}
        obj["value"] = x[0]
        obj["name"] = x[2]
        datas.append(obj)
    # print(datas)
    # for x in datas:
    #     value.append(x["value"])
    #     name.append(x["name"])
    # qws["value"] = value
    # qws["name"] = name
    # print(qws)
    return datas

# insert路由，写入数据
@app.route("/insert",methods = ["POST"])
def insert():
    # 判断类型
    if request.method == "POST":
        res = request.json # 数据解析成json
        qq = res["sender"]["id"] #qq号
        qname = res["sender"]["name"] #qq号名称
        gqq = res["group"]["id"] #qq群号
        gqname = res["group"]["name"]  # qq群名称
        messter = res["message"]["text"] #消息内容
        timeone = res["time"] #初始化time
        timetwo= time.localtime(int(timeone))
        newTime = time.strftime("%Y-%m-%d %H:%M:%S", timetwo) #解析time
        uuid = res["message"]["id"] #uuid
        # print(gqname,qq,qname,gqq,messter,newTime,uuid)
        # 数据存入数据库
        newWord = QQ(qq=qq,qname=qname,gqq=gqq,
                     gqname=gqname,messter=messter,
                     time=newTime,uuid=uuid)
        db.session.add(newWord)
        db.session.commit()

        return ""
# 主页面
@app.route("/index")
def index():
    return render_template("index.html")

# 获取ajax请求，返回相应数据
@app.route("/reqData",methods=["POST"])
def reqData():
    # 判断请求类型
    if request.method == "POST":
        # 获取ajax请求的json数据并解析
        reqdatas = request.get_data()
        reqdata = json.loads(reqdatas)
        # 判断数据
        if reqdata["type"] == "reqdata":
            # 创建并写入需要传入的json数据
            revData = {}
            revData["xwqwLine"] = xwqwLine() # 小屋数据：折线+抓状图
            revData["xwqwPie"] = xwqwPie() #小屋数据：一个函数两用：词云+饼状图
            revData["jzLine"] = jzLine() # 济职群数据：折线+柱状图
            revData["jzPie"] = jzPie() # 济职群数据：饼状图+词云图
            # 返回json数据
            return jsonify(revData)




# biu！~
if __name__ == "__main__":
    app.run(host = '0.0.0.0',
            port = 8080)

